{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "802c1ef7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import akshare as ak"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f161e930",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pydantic import BaseModel\n",
    "class FinancialMetrics(BaseModel):\n",
    "    ticker: str                          # 股票代码\n",
    "    report_period: str                   # 报告期\n",
    "    period: str                          # 期间\n",
    "    currency: str                        # 货币单位\n",
    "    market_cap: float | None             # 市值\n",
    "    enterprise_value: float | None       # 企业价值\n",
    "    price_to_earnings_ratio: float | None          # 市盈率 (P/E)\n",
    "    price_to_book_ratio: float | None              # 市净率 (P/B)\n",
    "    price_to_sales_ratio: float | None             # 市销率 (P/S)\n",
    "    enterprise_value_to_ebitda_ratio: float | None # 企业价值/EBITDA比率\n",
    "    enterprise_value_to_revenue_ratio: float | None# 企业价值/营收比率\n",
    "    free_cash_flow_yield: float | None   # 自由现金流收益率\n",
    "    peg_ratio: float | None              # PEG比率 (市盈增长比率)\n",
    "    gross_margin: float | None           # 毛利率\n",
    "    operating_margin: float | None       # 营业利润率\n",
    "    net_margin: float | None             # 净利润率\n",
    "    return_on_equity: float | None       # 股本回报率 (ROE)\n",
    "    return_on_assets: float | None       # 资产回报率 (ROA)\n",
    "    return_on_invested_capital: float | None  # 投入资本回报率 (ROIC)\n",
    "    asset_turnover: float | None         # 资产周转率\n",
    "    inventory_turnover: float | None     # 存货周转率\n",
    "    receivables_turnover: float | None   # 应收账款周转率\n",
    "    days_sales_outstanding: float | None# 应收账款周转天数\n",
    "    operating_cycle: float | None        # 营业周期\n",
    "    working_capital_turnover: float | None # 营运资本周转率\n",
    "    current_ratio: float | None          # 流动比率\n",
    "    quick_ratio: float | None            # 速动比率\n",
    "    cash_ratio: float | None             # 现金比率\n",
    "    operating_cash_flow_ratio: float | None # 经营现金流比率\n",
    "    debt_to_equity: float | None         # 负债权益比率\n",
    "    debt_to_assets: float | None         # 负债资产比率\n",
    "    interest_coverage: float | None      # 利息覆盖率\n",
    "    revenue_growth: float | None         # 营收增长率\n",
    "    earnings_growth: float | None        # 盈利增长率\n",
    "    book_value_growth: float | None      # 账面价值增长率\n",
    "    earnings_per_share_growth: float | None # 每股收益增长率\n",
    "    free_cash_flow_growth: float | None  # 自由现金流增长率\n",
    "    operating_income_growth: float | None# 营业利润增长率\n",
    "    ebitda_growth: float | None          # EBITDA增长率\n",
    "    payout_ratio: float | None           # 派息比率\n",
    "    earnings_per_share: float | None     # 每股收益 (EPS)\n",
    "    book_value_per_share: float | None   # 每股账面价值\n",
    "    free_cash_flow_per_share: float | None # 每股自由现金流\n",
    "\n",
    "\n",
    "class FinancialMetricsResponse(BaseModel):\n",
    "    financial_metrics: list[FinancialMetrics]\n",
    "\n",
    "\n",
    "class LineItem(BaseModel):\n",
    "    ticker: str\n",
    "    report_period: str\n",
    "    period: str\n",
    "    currency: str\n",
    "\n",
    "    # Allow additional fields dynamically\n",
    "    model_config = {\"extra\": \"allow\"}\n",
    "\n",
    "\n",
    "class LineItemResponse(BaseModel):\n",
    "    search_results: list[LineItem]\n",
    "\n",
    "\n",
    "class InsiderTrade(BaseModel):\n",
    "    ticker: str                          # 股票代码\n",
    "    issuer: str | None                   # 发行公司\n",
    "    name: str | None                     # 姓名（内部人姓名）\n",
    "    title: str | None                    # 职位\n",
    "    is_board_director: bool | None       # 是否董事\n",
    "    transaction_date: str | None         # 交易日期\n",
    "    transaction_shares: float | None     # 交易股数\n",
    "    transaction_price_per_share: float | None  # 每股交易价格\n",
    "    transaction_value: float | None      # 交易总价值\n",
    "    shares_owned_before_transaction: float | None  # 交易前持股数\n",
    "    shares_owned_after_transaction: float | None   # 交易后持股数\n",
    "    security_title: str | None           # 证券类型\n",
    "    filing_date: str                     # 备案日期（SEC文件提交日期）\n",
    "\n",
    "\n",
    "class InsiderTradeResponse(BaseModel):\n",
    "    insider_trades: list[InsiderTrade]\n",
    "\n",
    "\n",
    "class CompanyNews(BaseModel):\n",
    "    ticker: str\n",
    "    title: str\n",
    "    author: str\n",
    "    source: str\n",
    "    date: str\n",
    "    url: str\n",
    "    sentiment: str | None = None\n",
    "\n",
    "\n",
    "class CompanyNewsResponse(BaseModel):\n",
    "    news: list[CompanyNews]\n",
    "\n",
    "\n",
    "class CompanyFacts(BaseModel):\n",
    "    ticker: str                          # 股票代码\n",
    "    name: str                            # 公司名称\n",
    "    cik: str | None = None               # 中央索引码 (SEC公司识别码)\n",
    "    industry: str | None = None           # 所属行业\n",
    "    sector: str | None = None             # 所属板块\n",
    "    category: str | None = None           # 分类\n",
    "    exchange: str | None = None           # 上市交易所\n",
    "    is_active: bool | None = None         # 是否活跃上市\n",
    "    listing_date: str | None = None       # 上市日期\n",
    "    location: str | None = None           # 总部所在地\n",
    "    market_cap: float | None = None       # 市值\n",
    "    number_of_employees: int | None = None # 员工总数\n",
    "    sec_filings_url: str | None = None    # SEC申报文件链接\n",
    "    sic_code: str | None = None           # 标准产业分类代码 (SIC)\n",
    "    sic_industry: str | None = None       # SIC行业分类\n",
    "    sic_sector: str | None = None         # SIC板块分类\n",
    "    website_url: str | None = None        # 公司官网\n",
    "    weighted_average_shares: int | None = None  # 加权平均流通股数\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5d1d8b44",
   "metadata": {},
   "outputs": [],
   "source": [
    "ticker = \"sz002594\"\n",
    "financial_report_sina_df = ak.stock_financial_report_sina(stock=ticker, symbol=\"资产负债表\")\n",
    "financial_report_sina_lr = ak.stock_financial_report_sina(stock=ticker, symbol=\"利润表\")\n",
    "financial_report_sina_xj = ak.stock_financial_report_sina(stock=ticker, symbol=\"现金流量表\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "916b3ef9",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import pandas as pd\n",
    "merged_df = pd.merge(\n",
    "    financial_report_sina_df, \n",
    "    financial_report_sina_lr, \n",
    "    on='报告日', \n",
    "    how='outer'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ca775535",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(\n",
    "    merged_df,\n",
    "    financial_report_sina_xj,\n",
    "    on='报告日',\n",
    "    how='outer'\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ba3ad5de",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 再次处理重复列\n",
    "duplicate_cols = [col for col in merged_df.columns if col.endswith('_x')]\n",
    "merged_df = merged_df.drop(columns=duplicate_cols)\n",
    "merged_df.columns = [col[:-2] if col.endswith('_y') else col for col in merged_df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "5796ca6b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "合并后的数据框大小: (71, 292)\n",
      "合并后的列名: ['报告日', '流动资产', '货币资金', '结算备付金', '拆出资金', '交易性金融资产', '买入返售金融资产', '衍生金融资产', '应收票据及应收账款', '应收票据', '应收账款', '应收款项融资', '预付款项', '应收股利', '应收利息', '应收保费', '应收分保账款', '应收分保合同准备金', '应收出口退税', '应收补贴款', '应收保证金', '内部应收款', '其他应收款', '其他应收款(合计)', '存货', '划分为持有待售的资产', '待摊费用', '待处理流动资产损益', '一年内到期的非流动资产', '其他流动资产', '流动资产合计', '非流动资产', '发放贷款及垫款', '债权投资', '其他债权投资', '以公允价值计量且其变动计入其他综合收益的金融资产', '以摊余成本计量的金融资产', '可供出售金融资产', '长期股权投资', '投资性房地产', '长期应收款', '其他权益工具投资', '其他非流动金融资产', '其他长期投资', '固定资产原值', '累计折旧', '固定资产净值', '固定资产减值准备', '在建工程合计', '在建工程', '工程物资', '固定资产净额', '固定资产清理', '固定资产及清理合计', '生产性生物资产', '公益性生物资产', '油气资产', '合同资产', '使用权资产', '无形资产', '开发支出', '商誉', '长期待摊费用', '股权分置流通权', '递延所得税资产', '其他非流动资产', '非流动资产合计', '资产总计', '流动负债', '短期借款', '向中央银行借款', '吸收存款及同业存放', '拆入资金', '交易性金融负债', '衍生金融负债', '应付票据及应付账款', '应付票据', '应付账款', '预收款项', '合同负债', '卖出回购金融资产款', '应付手续费及佣金', '应付职工薪酬', '应交税费', '应付利息', '应付股利', '应付保证金', '内部应付款', '其他应付款', '其他应付款合计', '其他应交款', '担保责任赔偿准备金', '应付分保账款', '保险合同准备金', '代理买卖证券款', '代理承销证券款', '国际票证结算', '国内票证结算', '预提费用', '预计流动负债', '应付短期债券', '划分为持有待售的负债', '一年内的递延收益', '一年内到期的非流动负债', '其他流动负债', '流动负债合计', '非流动负债', '长期借款', '应付债券', '应付债券：优先股', '应付债券：永续债', '租赁负债', '长期应付职工薪酬', '长期应付款', '长期应付款合计', '专项应付款', '预计非流动负债', '长期递延收益', '递延所得税负债', '其他非流动负债', '非流动负债合计', '负债合计', '所有者权益', '实收资本(或股本)', '其他权益工具', '优先股', '永续债', '资本公积', '减:库存股', '专项储备', '盈余公积', '一般风险准备', '未确定的投资损失', '未分配利润', '拟分配现金股利', '外币报表折算差额', '归属于母公司股东权益合计', '少数股东权益', '所有者权益(或股东权益)合计', '负债和所有者权益(或股东权益)总计', '营业总收入', '营业收入', '利息收入', '已赚保费', '手续费及佣金收入', '房地产销售收入', '其他业务收入', '营业总成本', '营业成本', '手续费及佣金支出', '房地产销售成本', '退保金', '赔付支出净额', '提取保险合同准备金净额', '保单红利支出', '分保费用', '其他业务成本', '营业税金及附加', '研发费用', '销售费用', '管理费用', '财务费用', '利息费用', '利息支出', '投资收益', '对联营企业和合营企业的投资收益', '以摊余成本计量的金融资产终止确认产生的收益', '汇兑收益', '净敞口套期收益', '公允价值变动收益', '期货损益', '托管收益', '补贴收入', '其他收益', '资产减值损失', '信用减值损失', '其他业务利润', '资产处置收益', '营业利润', '营业外收入', '非流动资产处置利得', '营业外支出', '非流动资产处置损失', '利润总额', '所得税费用', '未确认投资损失', '净利润', '持续经营净利润', '终止经营净利润', '归属于母公司所有者的净利润', '被合并方在合并前实现净利润', '少数股东损益', '其他综合收益', '归属于母公司所有者的其他综合收益', '（一）以后不能重分类进损益的其他综合收益', '重新计量设定受益计划变动额', '权益法下不能转损益的其他综合收益', '其他权益工具投资公允价值变动', '企业自身信用风险公允价值变动', '（二）以后将重分类进损益的其他综合收益', '权益法下可转损益的其他综合收益', '可供出售金融资产公允价值变动损益', '其他债权投资公允价值变动', '金融资产重分类计入其他综合收益的金额', '其他债权投资信用减值准备', '持有至到期投资重分类为可供出售金融资产损益', '现金流量套期储备', '现金流量套期损益的有效部分', '外币财务报表折算差额', '其他', '归属于少数股东的其他综合收益', '综合收益总额', '归属于母公司所有者的综合收益总额', '归属于少数股东的综合收益总额', '基本每股收益', '稀释每股收益', '数据源', '是否审计', '公告日期', '币种', '类型', '更新日期', '经营活动产生的现金流量', '销售商品、提供劳务收到的现金', '客户存款和同业存放款项净增加额', '向中央银行借款净增加额', '向其他金融机构拆入资金净增加额', '收到原保险合同保费取得的现金', '收到再保险业务现金净额', '保户储金及投资款净增加额', '处置交易性金融资产净增加额', '收取利息、手续费及佣金的现金', '拆入资金净增加额', '回购业务资金净增加额', '收到的税费返还', '收到的其他与经营活动有关的现金', '经营活动现金流入小计', '购买商品、接受劳务支付的现金', '客户贷款及垫款净增加额', '存放中央银行和同业款项净增加额', '支付原保险合同赔付款项的现金', '支付利息、手续费及佣金的现金', '支付保单红利的现金', '支付给职工以及为职工支付的现金', '支付的各项税费', '支付的其他与经营活动有关的现金', '经营活动现金流出小计', '经营活动产生的现金流量净额', '投资活动产生的现金流量', '收回投资所收到的现金', '取得投资收益收到的现金', '处置固定资产、无形资产和其他长期资产所收回的现金净额', '处置子公司及其他营业单位收到的现金净额', '收到的其他与投资活动有关的现金', '减少质押和定期存款所收到的现金', '处置可供出售金融资产净增加额', '投资活动现金流入小计', '购建固定资产、无形资产和其他长期资产所支付的现金', '投资所支付的现金', '质押贷款净增加额', '取得子公司及其他营业单位支付的现金净额', '增加质押和定期存款所支付的现金', '支付的其他与投资活动有关的现金', '投资活动现金流出小计', '投资活动产生的现金流量净额', '筹资活动产生的现金流量', '吸收投资收到的现金', '子公司吸收少数股东投资收到的现金', '取得借款收到的现金', '发行债券收到的现金', '收到其他与筹资活动有关的现金', '筹资活动现金流入小计', '偿还债务支付的现金', '分配股利、利润或偿付利息所支付的现金', '子公司支付给少数股东的股利、利润', '支付其他与筹资活动有关的现金', '筹资活动现金流出小计', '筹资活动产生的现金流量净额', '汇率变动对现金及现金等价物的影响', '现金及现金等价物净增加额', '期初现金及现金等价物余额', '现金的期末余额', '现金的期初余额', '现金等价物的期末余额', '现金等价物的期初余额', '期末现金及现金等价物余额', '数据源', '是否审计', '公告日期', '币种', '类型', '更新日期']\n"
     ]
    }
   ],
   "source": [
    "# 第三步：对结果按报告日排序\n",
    "merged_df = merged_df.sort_values('报告日', ascending=False)\n",
    "\n",
    "# 检查合并结果\n",
    "print(f\"合并后的数据框大小: {merged_df.shape}\")\n",
    "print(f\"合并后的列名: {merged_df.columns.tolist()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f50174b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         报告日            负债合计\n",
      "70  20250331  594365580000.0\n",
      "69  20241231  584667646000.0\n",
      "68  20240930  595456788000.0\n",
      "67  20240630  531633632000.0\n",
      "66  20240331  522836586000.0\n",
      "65  20231231  529085557000.0\n",
      "64  20230930  482216639000.0\n",
      "63  20230630  460732251000.0\n",
      "62  20230331  421018155000.0\n",
      "61  20221231  372470809000.0\n"
     ]
    }
   ],
   "source": [
    "print(merged_df[['报告日','负债合计']].head(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f0ad5d9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "stock_financial_debt_ths_df = ak.stock_financial_debt_ths(symbol=\"002594\", indicator=\"按报告期\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4fb29765",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          报告期 报表核心指标 *所有者权益（或股东权益）合计     *资产合计     *负债合计 *归属于母公司所有者权益合计  \\\n",
      "0  2025-03-31               2461.62亿  8405.27亿  5943.66亿       2333.61亿   \n",
      "1  2024-12-31               1986.88亿  7833.56亿  5846.68亿       1852.51亿   \n",
      "2  2024-09-30               1688.03亿  7642.60亿  5954.57亿       1554.62亿   \n",
      "3  2024-06-30               1546.11亿  6862.45亿  5316.34亿       1427.87亿   \n",
      "4  2024-03-31               1549.47亿  6777.84亿  5228.37亿       1430.90亿   \n",
      "\n",
      "  报表全部指标 流动资产      货币资金  交易性金融资产  ... 实收资本（或股本）     资本公积   减：库存股    其他综合收益  \\\n",
      "0              1174.07亿  359.84亿  ...    30.39亿  995.69亿   7.24亿    11.98亿   \n",
      "1              1027.39亿  405.11亿  ...    29.09亿  606.79亿   7.24亿    14.41亿   \n",
      "2               663.19亿  254.15亿  ...    29.09亿  619.64亿   7.24亿     5.62亿   \n",
      "3               530.53亿  173.88亿  ...    29.09亿  619.14亿  12.67亿  8822.40万   \n",
      "4               867.97亿   93.85亿  ...    29.11亿  621.78亿  12.67亿     1.78亿   \n",
      "\n",
      "     盈余公积     未分配利润 归属于母公司所有者权益合计   少数股东权益 所有者权益（或股东权益）合计 负债和所有者权益（或股东权益）合计  \n",
      "0  73.74亿  1078.74亿      2333.61亿  128.00亿       2461.62亿          8405.27亿  \n",
      "1  73.74亿   986.48亿      1852.51亿  134.37亿       1986.88亿          7833.56亿  \n",
      "2  73.74亿   833.50亿      1554.62亿  133.40亿       1688.03亿          7642.60亿  \n",
      "3  73.74亿   717.43亿      1427.87亿  118.24亿       1546.11亿          6862.45亿  \n",
      "4  73.74亿   716.93亿      1430.90亿  118.57亿       1549.47亿          6777.84亿  \n",
      "\n",
      "[5 rows x 78 columns]\n"
     ]
    }
   ],
   "source": [
    "print(stock_financial_debt_ths_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f2c5bb34",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['报告期', '报表核心指标', '*所有者权益（或股东权益）合计', '*资产合计', '*负债合计', '*归属于母公司所有者权益合计', '报表全部指标', '流动资产', '货币资金', '交易性金融资产', '应收票据及应收账款', '其中：应收票据', '应收账款', '预付款项', '其他应收款合计', '其中：应收利息', '其他应收款', '存货', '一年内到期的非流动资产', '其他流动资产', '总现金', '流动资产合计', '非流动资产', '可供出售金融资产', '长期股权投资', '其他权益工具投资', '其他非流动金融资产', '投资性房地产', '固定资产合计', '其中：固定资产', '在建工程合计', '其中：在建工程', '工程物资', '无形资产', '商誉', '长期待摊费用', '递延所得税资产', '其他非流动资产', '非流动资产合计', '资产合计', '流动负债', '短期借款', '以公允价值计量且其变动计入当期损益的金融负债', '衍生金融负债', '应付票据及应付账款', '其中：应付票据', '应付账款', '预收款项', '合同负债', '应付职工薪酬', '应交税费', '其他应付款合计', '其中：应付利息', '应付股利', '其他应付款', '一年内到期的非流动负债', '其他流动负债', '流动负债合计', '非流动负债', '长期借款', '应付债券', '长期应付款合计', '其中：长期应付款', '递延所得税负债', '其他非流动负债', '非流动负债合计', '负债合计', '所有者权益（或股东权益）', '实收资本（或股本）', '资本公积', '减：库存股', '其他综合收益', '盈余公积', '未分配利润', '归属于母公司所有者权益合计', '少数股东权益', '所有者权益（或股东权益）合计', '负债和所有者权益（或股东权益）合计']\n"
     ]
    }
   ],
   "source": [
    "print(stock_financial_debt_ths_df.columns.to_list())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "67fc4025",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          报告期 流动负债\n",
      "0  2025-03-31     \n",
      "1  2024-12-31     \n",
      "2  2024-09-30     \n",
      "3  2024-06-30     \n",
      "4  2024-03-31     \n",
      "5  2023-12-31     \n",
      "6  2023-09-30     \n",
      "7  2023-06-30     \n",
      "8  2023-03-31     \n",
      "9  2022-12-31     \n"
     ]
    }
   ],
   "source": [
    "print(stock_financial_debt_ths_df[['报告期','流动负债']][0:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ba46b76e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         报告日         报告期 report_date\n",
      "70  20250331  2025-03-31  2025-03-31\n",
      "69  20241231  2024-12-31  2024-12-31\n",
      "68  20240930  2024-09-30  2024-09-30\n",
      "67  20240630  2024-06-30  2024-06-30\n",
      "66  20240331  2024-03-31  2024-03-31\n",
      "合并后的数据框大小: (71, 373)\n"
     ]
    }
   ],
   "source": [
    "merged_df['报告日_dt'] = pd.to_datetime(merged_df['报告日'], format='%Y%m%d', errors='coerce')\n",
    "stock_financial_debt_ths_df['报告期_dt'] = pd.to_datetime(stock_financial_debt_ths_df['报告期'], format='%Y-%m-%d', errors='coerce')\n",
    "\n",
    "merged_all_df = pd.merge(\n",
    "    merged_df,\n",
    "    stock_financial_debt_ths_df,\n",
    "    left_on='报告日_dt',\n",
    "    right_on='报告期_dt',\n",
    "    how='outer'\n",
    ")\n",
    "\n",
    "# 合并后的统一日期字段\n",
    "merged_all_df['report_date'] = merged_all_df['报告日_dt'].combine_first(merged_all_df['报告期_dt'])\n",
    "merged_all_df = merged_all_df.sort_values('report_date', ascending=False)\n",
    "print(merged_all_df[['报告日', '报告期', 'report_date']].head())\n",
    "print(f\"合并后的数据框大小: {merged_all_df.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "453480ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 再次处理重复列\n",
    "duplicate_cols = [col for col in merged_all_df.columns if col.endswith('_x')]\n",
    "merged_df = merged_all_df.drop(columns=duplicate_cols)\n",
    "merged_all_df.columns = [col[:-2] if col.endswith('_y') else col for col in merged_all_df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c9355d0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in ['净利润', '所得税费用', '财务费用', '累计折旧', '长期待摊费用']:\n",
    "    merged_all_df[col] = pd.to_numeric(merged_all_df[col], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "7244907d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['所有者权益', '所有者权益(或股东权益)合计', '负债和所有者权益(或股东权益)总计', '归属于母公司所有者的净利润', '归属于母公司所有者的其他综合收益', '归属于母公司所有者的综合收益总额', '*所有者权益（或股东权益）合计', '*归属于母公司所有者权益合计', '所有者权益（或股东权益）', '归属于母公司所有者权益合计', '所有者权益（或股东权益）合计', '负债和所有者权益（或股东权益）合计']\n"
     ]
    }
   ],
   "source": [
    "asset_cols = [col for col in merged_all_df.columns if '所有者' in col]\n",
    "print(asset_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "0cccbf06",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   所有者权益  所有者权益(或股东权益)合计\n",
      "70   NaN  246161565000.0\n",
      "69   NaN  198688209000.0\n",
      "68   NaN  168802727000.0\n",
      "67   NaN  154611078000.0\n",
      "66   NaN  154947035000.0\n",
      "..   ...             ...\n",
      "4    NaN    5015530000.0\n",
      "3    NaN    3994780000.0\n",
      "2    NaN    3926919000.0\n",
      "1    NaN    3399898000.0\n",
      "0    NaN    2632379000.0\n",
      "\n",
      "[71 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged_all_df[['所有者权益','所有者权益(或股东权益)合计']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abdf029a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_10220/3035874008.py:23: FutureWarning: Downcasting object dtype arrays on .fillna, .ffill, .bfill is deprecated and will change in a future version. Call result.infer_objects(copy=False) instead. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`\n",
      "  merged_all_df['长期待摊费用_x'].fillna(0).astype(float)\n",
      "/tmp/ipykernel_10220/3035874008.py:69: FutureWarning: Downcasting object dtype arrays on .fillna, .ffill, .bfill is deprecated and will change in a future version. Call result.infer_objects(copy=False) instead. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`\n",
      "  merged_all_df['营业收入'].pct_change() * 100\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "'每股净资产'",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mKeyError\u001b[39m                                  Traceback (most recent call last)",
      "\u001b[36mFile \u001b[39m\u001b[32m~/anaconda3/envs/rag/lib/python3.13/site-packages/pandas/core/indexes/base.py:3805\u001b[39m, in \u001b[36mIndex.get_loc\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m   3804\u001b[39m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[32m-> \u001b[39m\u001b[32m3805\u001b[39m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43m_engine\u001b[49m\u001b[43m.\u001b[49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcasted_key\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m   3806\u001b[39m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "\u001b[36mFile \u001b[39m\u001b[32mindex.pyx:167\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine.get_loc\u001b[39m\u001b[34m()\u001b[39m\n",
      "\u001b[36mFile \u001b[39m\u001b[32mindex.pyx:191\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine.get_loc\u001b[39m\u001b[34m()\u001b[39m\n",
      "\u001b[36mFile \u001b[39m\u001b[32mindex.pyx:234\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine._get_loc_duplicates\u001b[39m\u001b[34m()\u001b[39m\n",
      "\u001b[36mFile \u001b[39m\u001b[32mindex.pyx:242\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine._maybe_get_bool_indexer\u001b[39m\u001b[34m()\u001b[39m\n",
      "\u001b[36mFile \u001b[39m\u001b[32mindex.pyx:134\u001b[39m, in \u001b[36mpandas._libs.index._unpack_bool_indexer\u001b[39m\u001b[34m()\u001b[39m\n",
      "\u001b[31mKeyError\u001b[39m: '每股净资产'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[31mKeyError\u001b[39m                                  Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[32]\u001b[39m\u001b[32m, line 78\u001b[39m\n\u001b[32m     72\u001b[39m financial_metrics_df[\u001b[33m'\u001b[39m\u001b[33mearnings_growth\u001b[39m\u001b[33m'\u001b[39m] = (\n\u001b[32m     73\u001b[39m     merged_all_df[\u001b[33m'\u001b[39m\u001b[33m净利润\u001b[39m\u001b[33m'\u001b[39m].pct_change() * \u001b[32m100\u001b[39m\n\u001b[32m     74\u001b[39m )\n\u001b[32m     76\u001b[39m \u001b[38;5;66;03m# 10. 账面价值增长率 = (本期每股净资产-上期每股净资产)/上期每股净资产\u001b[39;00m\n\u001b[32m     77\u001b[39m financial_metrics_df[\u001b[33m'\u001b[39m\u001b[33mbook_value_growth\u001b[39m\u001b[33m'\u001b[39m] = (\n\u001b[32m---> \u001b[39m\u001b[32m78\u001b[39m     \u001b[43mmerged_all_df\u001b[49m\u001b[43m[\u001b[49m\u001b[33;43m'\u001b[39;49m\u001b[33;43m每股净资产\u001b[39;49m\u001b[33;43m'\u001b[39;49m\u001b[43m]\u001b[49m.pct_change() * \u001b[32m100\u001b[39m\n\u001b[32m     79\u001b[39m )\n\u001b[32m     81\u001b[39m \u001b[38;5;66;03m# 11. 每股收益增长率 = (本期每股收益-上期每股收益)/上期每股收益\u001b[39;00m\n\u001b[32m     82\u001b[39m financial_metrics_df[\u001b[33m'\u001b[39m\u001b[33mearnings_per_share_growth\u001b[39m\u001b[33m'\u001b[39m] = (\n\u001b[32m     83\u001b[39m     merged_all_df[\u001b[33m'\u001b[39m\u001b[33m基本每股收益\u001b[39m\u001b[33m'\u001b[39m].pct_change() * \u001b[32m100\u001b[39m\n\u001b[32m     84\u001b[39m )\n",
      "\u001b[36mFile \u001b[39m\u001b[32m~/anaconda3/envs/rag/lib/python3.13/site-packages/pandas/core/frame.py:4102\u001b[39m, in \u001b[36mDataFrame.__getitem__\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m   4100\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m.columns.nlevels > \u001b[32m1\u001b[39m:\n\u001b[32m   4101\u001b[39m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m._getitem_multilevel(key)\n\u001b[32m-> \u001b[39m\u001b[32m4102\u001b[39m indexer = \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43mcolumns\u001b[49m\u001b[43m.\u001b[49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m   4103\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m is_integer(indexer):\n\u001b[32m   4104\u001b[39m     indexer = [indexer]\n",
      "\u001b[36mFile \u001b[39m\u001b[32m~/anaconda3/envs/rag/lib/python3.13/site-packages/pandas/core/indexes/base.py:3812\u001b[39m, in \u001b[36mIndex.get_loc\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m   3807\u001b[39m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(casted_key, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;129;01mor\u001b[39;00m (\n\u001b[32m   3808\u001b[39m         \u001b[38;5;28misinstance\u001b[39m(casted_key, abc.Iterable)\n\u001b[32m   3809\u001b[39m         \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28many\u001b[39m(\u001b[38;5;28misinstance\u001b[39m(x, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;28;01mfor\u001b[39;00m x \u001b[38;5;129;01min\u001b[39;00m casted_key)\n\u001b[32m   3810\u001b[39m     ):\n\u001b[32m   3811\u001b[39m         \u001b[38;5;28;01mraise\u001b[39;00m InvalidIndexError(key)\n\u001b[32m-> \u001b[39m\u001b[32m3812\u001b[39m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01merr\u001b[39;00m\n\u001b[32m   3813\u001b[39m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[32m   3814\u001b[39m     \u001b[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[32m   3815\u001b[39m     \u001b[38;5;66;03m#  InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[32m   3816\u001b[39m     \u001b[38;5;66;03m#  the TypeError.\u001b[39;00m\n\u001b[32m   3817\u001b[39m     \u001b[38;5;28mself\u001b[39m._check_indexing_error(key)\n",
      "\u001b[31mKeyError\u001b[39m: '每股净资产'"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "# 直接映射\n",
    "financial_metrics_df = pd.DataFrame()\n",
    "financial_metrics_df['report_period'] = merged_all_df['report_date']\n",
    "financial_metrics_df['currency'] = 'CNY'\n",
    "financial_metrics_df['gross_margin'] = merged_all_df.get('毛利率')\n",
    "financial_metrics_df['operating_margin'] = merged_all_df.get('营业利润率')\n",
    "financial_metrics_df['net_margin'] = merged_all_df.get('净利润率')\n",
    "financial_metrics_df['current_ratio'] = merged_all_df.get('流动比率')\n",
    "financial_metrics_df['quick_ratio'] = merged_all_df.get('速动比率')\n",
    "financial_metrics_df['cash_ratio'] = merged_all_df.get('现金比率')\n",
    "financial_metrics_df['debt_to_assets'] = merged_all_df.get('资产负债率')\n",
    "financial_metrics_df['earnings_per_share'] = merged_all_df.get('基本每股收益')\n",
    "financial_metrics_df['book_value_per_share'] = merged_all_df.get('每股净资产')\n",
    "financial_metrics_df['return_on_equity'] = merged_all_df.get('净资产收益率')  # 如有\n",
    "financial_metrics_df['return_on_assets'] = merged_all_df.get('总资产报酬率')  # 如有\n",
    "financial_metrics_df['EBITDA'] = (\n",
    "    merged_all_df['净利润'].fillna(0).astype(float) +\n",
    "    merged_all_df['所得税费用'].fillna(0).astype(float) +\n",
    "    merged_all_df['财务费用'].fillna(0).astype(float) +\n",
    "    merged_all_df['累计折旧'].fillna(0).astype(float) +\n",
    "    merged_all_df['长期待摊费用_x'].fillna(0).astype(float)\n",
    ")\n",
    "# 计算型\n",
    "financial_metrics_df['净资产'] = merged_all_df['所有者权益(或股东权益)合计']\n",
    "financial_metrics_df['营业收入'] = merged_all_df['营业收入']\n",
    "financial_metrics_df['营业成本'] = merged_all_df['营业成本']\n",
    "financial_metrics_df['实收资本(或股本)'] = merged_all_df['实收资本(或股本)']\n",
    "financial_metrics_df['流动负债'] = merged_all_df['流动负债_x']\n",
    "financial_metrics_df['流动资产'] = merged_all_df['流动资产_x']\n",
    "financial_metrics_df['所得税费用'] = merged_all_df['所得税费用']\n",
    "financial_metrics_df['财务费用'] = merged_all_df['财务费用']\n",
    "financial_metrics_df['净利润'] = merged_all_df['净利润']\n",
    "financial_metrics_df['debt_to_equity'] = merged_all_df['负债合计_x'] / merged_all_df['所有者权益(或股东权益)合计']\n",
    "financial_metrics_df['asset_turnover'] = merged_all_df['营业收入'] / merged_all_df['资产总计']\n",
    "financial_metrics_df['inventory_turnover'] = merged_all_df['营业成本'] / merged_all_df['存货_x']\n",
    "financial_metrics_df['receivables_turnover'] = merged_all_df['营业收入'] / merged_all_df['应收账款_x']\n",
    "financial_metrics_df['operating_cash_flow_ratio'] = merged_all_df['经营活动产生的现金流量净额'] / merged_all_df['流动负债_x']\n",
    "financial_metrics_df['free_cash_flow_per_share'] = (merged_all_df['经营活动产生的现金流量净额']) / merged_all_df['实收资本(或股本)']\n",
    "financial_metrics_df['free_cash_flow'] = merged_all_df['经营活动产生的现金流量净额']\n",
    "# 3. 投入资本回报率 ROIC = (净利润 - 分红) / (总资产 - 流动负债)\n",
    "financial_metrics_df['return_on_invested_capital'] = (\n",
    "    merged_all_df['净利润'] / (merged_all_df['资产总计'] - merged_all_df['流动负债_x'])\n",
    ")\n",
    "\n",
    "# 4. 应收账款周转天数 = 365 / 应收账款周转率\n",
    "financial_metrics_df['days_sales_outstanding'] = (\n",
    "    365 / financial_metrics_df['receivables_turnover']\n",
    ")\n",
    "\n",
    "# 5. 营业周期 = 应收账款周转天数 + 存货周转天数（存货周转天数=365/存货周转率）\n",
    "financial_metrics_df['operating_cycle'] = (\n",
    "    financial_metrics_df['days_sales_outstanding'] + \n",
    "    365 / financial_metrics_df['inventory_turnover']\n",
    ")\n",
    "\n",
    "# 6. 营运资本周转率 = 营业收入 / 营运资本（营运资本=流动资产-流动负债）\n",
    "financial_metrics_df['working_capital_turnover'] = (\n",
    "    merged_all_df['营业收入'] / (merged_all_df['流动资产'] - merged_all_df['流动负债_x'])\n",
    ")\n",
    "\n",
    "# 7. 利息覆盖率 = 息税前利润 / 利息费用（息税前利润=净利润+所得税+财务费用）\n",
    "financial_metrics_df['interest_coverage'] = (\n",
    "    (merged_all_df['净利润'] + merged_all_df['所得税费用'] + merged_all_df['财务费用']) / merged_all_df['财务费用']\n",
    ")\n",
    "\n",
    "# 8. 营收增长率 = (本期营业收入-上期营业收入)/上期营业收入\n",
    "financial_metrics_df['revenue_growth'] = (\n",
    "    merged_all_df['营业收入'].pct_change() * 100\n",
    ")\n",
    "# 9. 盈利增长率 = (本期净利润-上期净利润)/上期净利润\n",
    "financial_metrics_df['earnings_growth'] = (\n",
    "    merged_all_df['净利润'].pct_change() * 100\n",
    ")\n",
    "\n",
    "# 10. 账面价值增长率 = (本期每股净资产-上期每股净资产)/上期每股净资产\n",
    "financial_metrics_df['book_value_growth'] = (\n",
    "    merged_all_df['每股净资产'].pct_change() * 100\n",
    ")\n",
    "\n",
    "# 11. 每股收益增长率 = (本期每股收益-上期每股收益)/上期每股收益\n",
    "financial_metrics_df['earnings_per_share_growth'] = (\n",
    "    merged_all_df['基本每股收益'].pct_change() * 100\n",
    ")\n",
    "\n",
    "# 12. 自由现金流增长率 = (本期经营现金流-上期经营现金流)/上期经营现金流\n",
    "financial_metrics_df['free_cash_flow_growth'] = (\n",
    "    merged_all_df['经营活动产生的现金流量净额'].pct_change() * 100\n",
    ")\n",
    "\n",
    "# 13. 营业利润增长率 = (本期营业利润-上期营业利润)/上期营业利润\n",
    "financial_metrics_df['operating_income_growth'] = (\n",
    "    merged_all_df['营业利润'].pct_change() * 100\n",
    ")\n",
    "# 14. EBITDA增长率 = (本期EBITDA-上期EBITDA)/上期EBITDA\n",
    "financial_metrics_df['ebitda_growth'] = (\n",
    "    financial_metrics_df['EBITDA'].pct_change() * 100\n",
    ")\n",
    "\n",
    "# 15. 派息比率 = 每股分红 / 每股收益（如有分红数据）\n",
    "if '每股分红' in merged_all_df.columns:\n",
    "    financial_metrics_df['payout_ratio'] = merged_all_df['每股分红'] / merged_all_df['基本每股收益']\n",
    "else:\n",
    "    financial_metrics_df['payout_ratio'] = np.nan\n",
    "\n",
    "# 16. 加权平均流通股数（如有数据）\n",
    "if '加权平均流通股数' in merged_all_df.columns:\n",
    "    financial_metrics_df['weighted_average_shares'] = merged_all_df['加权平均流通股数']\n",
    "else:\n",
    "    financial_metrics_df['weighted_average_shares'] = np.nan\n",
    "# 缺失字段补None\n",
    "for col in [\n",
    "    'ticker', 'market_cap', 'enterprise_value', 'price_to_earnings_ratio', 'price_to_book_ratio',\n",
    "    'price_to_sales_ratio', 'enterprise_value_to_ebitda_ratio', 'enterprise_value_to_revenue_ratio',\n",
    "    'free_cash_flow_yield', 'peg_ratio', 'return_on_invested_capital', 'days_sales_outstanding',\n",
    "    'operating_cycle', 'working_capital_turnover', 'interest_coverage', 'revenue_growth',\n",
    "    'earnings_growth', 'book_value_growth', 'earnings_per_share_growth', 'free_cash_flow_growth',\n",
    "    'operating_income_growth', 'ebitda_growth', 'payout_ratio', 'weighted_average_shares'\n",
    "]:\n",
    "    financial_metrics_df[col] =  financial_metrics_df.get(col, 0)\n",
    "\n",
    "# 示例：补充ticker\n",
    "financial_metrics_df['ticker'] = \"sz002594\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de99ec43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8ca29f9884b54bb28d3468c66cef23f8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       代码    名称    最新价   涨跌幅  股东户数-本次  股东户数-上次  股东户数-增减  股东户数-增减比例      区间涨跌幅  \\\n",
      "0  920682  球冠电缆  10.17 -0.29    13697    11819     1878  15.889669  41.401274   \n",
      "1  300474   景嘉微  67.69  2.00    98115   100693    -2578  -2.560257  -5.844981   \n",
      "2  002922   伊戈尔  15.78 -0.63    53201    53766     -565  -1.050850  -4.739583   \n",
      "3  300523  辰安科技  21.77  1.16    14990    15459     -469  -3.033831 -10.268631   \n",
      "\n",
      "  股东户数统计截止日-本次 股东户数统计截止日-上次         户均持股市值        户均持股数量           总市值  \\\n",
      "0   2025-03-30   2024-12-31  219131.196612           NaN  3.001440e+09   \n",
      "1   2025-03-30   2025-03-20  434171.053017   5326.598614  4.259869e+10   \n",
      "2   2025-03-30   2025-03-20  134836.464961   7372.141332  7.173435e+09   \n",
      "3   2025-03-30   2025-03-20  321409.305069  15519.522215  4.817925e+09   \n",
      "\n",
      "           总股本        公告日期  \n",
      "0          NaN  2025-05-07  \n",
      "1  522619223.0  2025-04-16  \n",
      "2  392205291.0  2025-04-03  \n",
      "3  232637638.0  2025-04-01  \n"
     ]
    }
   ],
   "source": [
    "stock_zh_a_gdhs_df = ak.stock_zh_a_gdhs(symbol=\"20250330\")\n",
    "print(stock_zh_a_gdhs_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7beb5099",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['trade_date', 'pe', 'pe_ttm', 'pb', 'dv_ratio', 'dv_ttm', 'ps', 'ps_ttm', 'total_mv']\n",
      "   trade_date       pe   pe_ttm      pb  dv_ratio  dv_ttm      ps  ps_ttm  \\\n",
      "0  2011-06-30  23.7424  55.1660  3.2017       NaN     NaN  1.2366  1.2771   \n",
      "1  2011-07-01  26.1213  60.6935  3.5224       NaN     NaN  1.3605  1.4051   \n",
      "2  2011-07-04  28.7334  66.7628  3.8747       NaN     NaN  1.4966  1.5456   \n",
      "3  2011-07-05  30.8324  71.6400  4.1577       NaN     NaN  1.6059  1.6585   \n",
      "4  2011-07-06  31.1590  72.3986  4.2018       NaN     NaN  1.6229  1.6760   \n",
      "\n",
      "    total_mv  \n",
      "0  5991184.5  \n",
      "1  6591480.0  \n",
      "2  7250628.0  \n",
      "3  7780300.5  \n",
      "4  7862694.0  \n"
     ]
    }
   ],
   "source": [
    "#计算总市值---market_cap\n",
    "stock_a_indicator_lg_df = ak.stock_a_indicator_lg(symbol=\"002594\")\n",
    "print(stock_a_indicator_lg_df.columns.to_list())\n",
    "print(stock_a_indicator_lg_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "defcf52c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 假设 stock_a_indicator_lg_df 有 'trade_date'（格式如'2025-03-31'）和 'total_mv' 列\n",
    "stock_a_indicator_lg_df['trade_date'] = pd.to_datetime(stock_a_indicator_lg_df['trade_date'])\n",
    "financial_metrics_df['report_period'] = pd.to_datetime(financial_metrics_df['report_period'])\n",
    "\n",
    "# 按季度末日期向前查找最近的市值（常用方法：merge_asof）\n",
    "financial_metrics_df = financial_metrics_df.sort_values('report_period')\n",
    "stock_a_indicator_lg_df = stock_a_indicator_lg_df.sort_values('trade_date')\n",
    "\n",
    "financial_metrics_df = pd.merge_asof(\n",
    "    financial_metrics_df,\n",
    "    stock_a_indicator_lg_df[['trade_date', 'pe','pb','ps','total_mv']],\n",
    "    left_on='report_period',\n",
    "    right_on='trade_date',\n",
    "    direction='backward'\n",
    ")\n",
    "\n",
    "# 写入 market_cap 列\n",
    "financial_metrics_df['market_cap'] = financial_metrics_df['total_mv']\n",
    "financial_metrics_df['price_to_earnings_ratio'] = financial_metrics_df['pe']\n",
    "financial_metrics_df['price_to_book_ratio'] = financial_metrics_df['pb']\n",
    "financial_metrics_df['price_to_sales_ratio'] = financial_metrics_df['ps']\n",
    "financial_metrics_df['free_cash_flow_yield'] = financial_metrics_df['free_cash_flow'] / financial_metrics_df['market_cap']\n",
    "\n",
    "# 可选：去掉多余的 total_mv 和 trade_date 列\n",
    "financial_metrics_df = financial_metrics_df.drop(columns=['total_mv', 'pe', 'pb', 'ps','trade_date'])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05e81532",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算总资产。先合并，确保 enterprise_value 计算时两表一一对应\n",
    "financial_metrics_df['report_period'] = pd.to_datetime(financial_metrics_df['report_period'])\n",
    "merged_all_df['report_date'] = pd.to_datetime(merged_all_df['report_date'])\n",
    "def parse_chinese_number(s):\n",
    "    if pd.isnull(s):\n",
    "        return np.nan\n",
    "    if isinstance(s, (int, float)):\n",
    "        return s\n",
    "    s = str(s).replace(',', '')\n",
    "    if '亿' in s:\n",
    "        return float(s.replace('亿', '')) * 1e8\n",
    "    if '万' in s:\n",
    "        return float(s.replace('万', '')) * 1e4\n",
    "    try:\n",
    "        return float(s)\n",
    "    except:\n",
    "        return np.nan\n",
    "\n",
    "for col in ['负债和所有者权益（或股东权益）合计', '总现金']:\n",
    "    merged_all_df[col] = merged_all_df[col].apply(parse_chinese_number)\n",
    "    \n",
    "tmp = pd.merge(\n",
    "    financial_metrics_df,\n",
    "    merged_all_df[['report_date','负债和所有者权益（或股东权益）合计','总现金']],\n",
    "    left_on='report_period',\n",
    "    right_on='report_date',\n",
    "    how='left'\n",
    ")\n",
    "for col in ['market_cap', '负债和所有者权益（或股东权益）合计', '总现金']:\n",
    "    tmp[col] = pd.to_numeric(tmp[col], errors='coerce')\n",
    "    \n",
    "# 计算 enterprise_value\n",
    "tmp['enterprise_value'] = tmp['market_cap'] + tmp['负债和所有者权益（或股东权益）合计'] - tmp['总现金']\n",
    "\n",
    "# 回写到原表（如果需要）\n",
    "financial_metrics_df['enterprise_value'] = tmp['enterprise_value']\n",
    "financial_metrics_df['enterprise_value_to_ebitda_ratio'] = financial_metrics_df['enterprise_value']/ financial_metrics_df['EBITDA']\n",
    "financial_metrics_df['enterprise_value_to_revenue_ratio'] = financial_metrics_df['enterprise_value']/ financial_metrics_df['营业成本']\n",
    "financial_metrics_df['peg_ratio'] = financial_metrics_df['price_to_earnings_ratio'] / financial_metrics_df['净利润'].pct_change()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fafb49a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "financial_metrics_df: pppppppppppppppppppppp\n",
      "   report_period    market_cap  enterprise_value\n",
      "66    2024-03-31  5.911367e+07      5.816611e+11\n",
      "67    2024-06-30  7.280438e+07      6.158768e+11\n",
      "68    2024-09-30  8.940465e+07      6.726144e+11\n",
      "69    2024-12-31  8.223331e+07      6.401882e+11\n",
      "70    2025-03-31  1.139346e+08      6.872499e+11\n",
      "merged_all_df : pppppppppppppppppppppp\n",
      "    负债和所有者权益（或股东权益）合计           总现金 report_date\n",
      "70       8.405270e+11  1.533910e+11  2025-03-31\n",
      "69       7.833560e+11  1.432500e+11  2024-12-31\n",
      "68       7.642600e+11  9.173500e+10  2024-09-30\n",
      "67       6.862450e+11  7.044100e+10  2024-06-30\n",
      "66       6.777840e+11  9.618200e+10  2024-03-31\n",
      "tmp : pppppppppppppppppppppppppp\n",
      "   report_period    market_cap  负债和所有者权益（或股东权益）合计           总现金  \\\n",
      "66    2024-03-31  5.911367e+07       6.777840e+11  9.618200e+10   \n",
      "67    2024-06-30  7.280438e+07       6.862450e+11  7.044100e+10   \n",
      "68    2024-09-30  8.940465e+07       7.642600e+11  9.173500e+10   \n",
      "69    2024-12-31  8.223331e+07       7.833560e+11  1.432500e+11   \n",
      "70    2025-03-31  1.139346e+08       8.405270e+11  1.533910e+11   \n",
      "\n",
      "    enterprise_value report_date        EBITDA  \n",
      "66      5.816611e+11  2024-03-31  1.089964e+10  \n",
      "67      6.158768e+11  2024-06-30  1.410305e+11  \n",
      "68      6.726144e+11  2024-09-30  3.781150e+10  \n",
      "69      6.401882e+11  2024-12-31  1.989790e+11  \n",
      "70      6.872499e+11  2025-03-31  1.409534e+10  \n"
     ]
    }
   ],
   "source": [
    "print('financial_metrics_df: pppppppppppppppppppppp')\n",
    "print(financial_metrics_df[['report_period', 'market_cap','enterprise_value']].tail())\n",
    "print(\"merged_all_df : pppppppppppppppppppppp\")\n",
    "print(merged_all_df[['负债和所有者权益（或股东权益）合计', '总现金','report_date']].head())\n",
    "print(\"tmp : pppppppppppppppppppppppppp\")\n",
    "print(tmp[['report_period', 'market_cap', '负债和所有者权益（或股东权益）合计', '总现金', 'enterprise_value','report_date','EBITDA']].tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f3fc887",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   report_period    market_cap  enterprise_value  \\\n",
      "66    2024-03-31  5.911367e+07      5.816611e+11   \n",
      "67    2024-06-30  7.280438e+07      6.158768e+11   \n",
      "68    2024-09-30  8.940465e+07      6.726144e+11   \n",
      "69    2024-12-31  8.223331e+07      6.401882e+11   \n",
      "70    2025-03-31  1.139346e+08      6.872499e+11   \n",
      "\n",
      "    enterprise_value_to_ebitda_ratio        EBITDA  \n",
      "66                         53.365192  1.089964e+10  \n",
      "67                          4.366976  1.410305e+11  \n",
      "68                         17.788619  3.781150e+10  \n",
      "69                          3.217366  1.989790e+11  \n",
      "70                         48.757255  1.409534e+10  \n"
     ]
    }
   ],
   "source": [
    "print(financial_metrics_df[['report_period','market_cap','enterprise_value','enterprise_value_to_ebitda_ratio','EBITDA']].tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5d66ee4",
   "metadata": {},
   "outputs": [],
   "source": [
    "financial_metrics_df['enterprise_value_to_ebitda_ratio'] = financial_metrics_df['enterprise_value']/ financial_metrics_df['EBITDA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5866f3b2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   report_period  enterprise_value_to_ebitda_ratio  enterprise_value  \\\n",
      "66    2024-03-31                         53.365192      5.816611e+11   \n",
      "67    2024-06-30                          4.366976      6.158768e+11   \n",
      "68    2024-09-30                         17.788619      6.726144e+11   \n",
      "69    2024-12-31                          3.217366      6.401882e+11   \n",
      "70    2025-03-31                         48.757255      6.872499e+11   \n",
      "\n",
      "          EBITDA enterprise_value_to_revenue_ratio  \n",
      "66  1.089964e+10                           5.87096  \n",
      "67  1.410305e+11                          2.556991  \n",
      "68  3.781150e+10                          1.690183  \n",
      "69  1.989790e+11                          1.022589  \n",
      "70  1.409534e+10                            5.0468  \n"
     ]
    }
   ],
   "source": [
    "print(financial_metrics_df[['report_period','enterprise_value_to_ebitda_ratio','enterprise_value','EBITDA','enterprise_value_to_revenue_ratio']].tail())\n",
    "# 将 financial_metrics_df 转换为 FinancialMetricsResponse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34db79b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   report_date           净利润         所得税费用          财务费用          累计折旧  长期待摊费用\n",
      "70  2025-03-31  9.443140e+09  1.747743e+09 -1.908346e+09           NaN     NaN\n",
      "69  2024-12-31  4.158794e+10  8.092737e+09  1.216206e+09  1.430754e+11     NaN\n",
      "68  2024-09-30  2.624755e+10  5.071961e+09  1.027128e+09           NaN     NaN\n",
      "67  2024-06-30  1.411322e+10  3.114922e+09  6.900600e+07  1.184677e+11     NaN\n",
      "66  2024-03-31  4.770879e+09  9.464860e+08 -1.939610e+08           NaN     NaN\n"
     ]
    }
   ],
   "source": [
    "print(merged_all_df[['report_date','净利润','所得税费用','财务费用','累计折旧','长期待摊费用']].head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ea7136de",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['流动资产_x', '交易性金融资产_x', '买入返售金融资产', '衍生金融资产', '划分为持有待售的资产', '待处理流动资产损益', '一年内到期的非流动资产_x', '其他流动资产_x', '流动资产合计_x', '非流动资产_x', '以公允价值计量且其变动计入其他综合收益的金融资产', '以摊余成本计量的金融资产', '可供出售金融资产_x', '其他非流动金融资产_x', '固定资产原值', '固定资产净值', '固定资产减值准备', '固定资产净额', '固定资产清理', '固定资产及清理合计', '生产性生物资产', '公益性生物资产', '油气资产', '合同资产', '使用权资产', '无形资产_x', '递延所得税资产_x', '其他非流动资产_x', '非流动资产合计_x', '资产总计', '卖出回购金融资产款', '以摊余成本计量的金融资产终止确认产生的收益', '资产减值损失', '资产处置收益', '非流动资产处置利得', '非流动资产处置损失', '可供出售金融资产公允价值变动损益', '金融资产重分类计入其他综合收益的金额', '持有至到期投资重分类为可供出售金融资产损益', '处置交易性金融资产净增加额', '处置固定资产、无形资产和其他长期资产所收回的现金净额', '处置可供出售金融资产净增加额', '购建固定资产、无形资产和其他长期资产所支付的现金', '*资产合计', '流动资产', '交易性金融资产', '一年内到期的非流动资产', '其他流动资产', '流动资产合计', '非流动资产', '可供出售金融资产', '其他非流动金融资产', '固定资产合计', '其中：固定资产', '无形资产', '递延所得税资产', '其他非流动资产', '非流动资产合计', '资产合计']\n"
     ]
    }
   ],
   "source": [
    "asset_cols = [col for col in merged_all_df.columns if '资产' in col]\n",
    "print(asset_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e5fee28",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     经营活动产生的现金流量净额 购建固定资产、无形资产和其他长期资产所支付的现金\n",
      "70    8580961000.0            37276487000.0\n",
      "69  133453873000.0            97359768000.0\n",
      "68   56273315000.0            69517647000.0\n",
      "67   14178310000.0            47225661000.0\n",
      "66   10227984000.0            26094349000.0\n"
     ]
    }
   ],
   "source": [
    "print(merged_all_df[['经营活动产生的现金流量净额', '购建固定资产、无形资产和其他长期资产所支付的现金']].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e3e730b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "rag",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
